package saleMarket.dao;

import saleMarket.domain.Administrator;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.TreeSet;

/**
 * @author 徐元坤
 * @version 1.0  12-16-2020
 */

public class AdministratorDao {
    private static AdministratorDao administratorDao = new AdministratorDao();
    private AdministratorDao(){}
    public static AdministratorDao getInstance(){return administratorDao;}

    /**
     * @param conn      连接
     * @param conn 来自Service的Connection对象
     * @return Administrator对象
     * @throws SQLException
     */
    //查询单个顾客方法
    public Administrator find(Integer id, Connection conn) throws SQLException {
        Administrator administrator = null;
        //编辑预编译语句
        PreparedStatement find_pstmt = conn.prepareStatement
                ("SELECT * FROM administrator WHERE id = ?");
        //设置预编译参数
        find_pstmt.setInt(1,id);
        //执行查询并获取查询结果
        ResultSet find_resultSet = find_pstmt.executeQuery();
        //赋值
        if (find_resultSet.next()){
            String no = find_resultSet.getString("no");
            String name = find_resultSet.getString("name");

            //创建相应的顾客对象
            administrator = new Administrator(id,no,name);
        }
        //释放资源
        JdbcHelper.close(find_resultSet,find_pstmt);
        //返回查询结果
        return administrator;
    }
    /**
     * @param conn       连接
     * @param conn 来自Service的Connection对象
     * @return collection<Administrator></>
     * @throws SQLException
     */
    //查询所有顾客方法
    public Collection<Administrator> findAll(Connection conn) throws SQLException {
        Collection<Administrator> administrators = new ArrayList<Administrator>();
        //编辑预编译语句
        PreparedStatement findAll_pstmt = conn.prepareStatement("SELECT * FROM customer");
        //执行查询并获取查询结果
        ResultSet findAll_resultSet = findAll_pstmt.executeQuery();
        //赋值
        while (findAll_resultSet.next()){
            Integer id = findAll_resultSet.getInt("id");
            String no = findAll_resultSet.getString("no");
            String name = findAll_resultSet.getString("name");

            //创建相应的顾客对象
            Administrator administrator = new Administrator(id,no,name);
            //将查询出的单个顾客添加进集合中
            administrators.add(administrator);
        }
        //释放资源
        JdbcHelper.close(findAll_resultSet,findAll_pstmt);
        //返回查询结果
        return administrators;
    }
    /**
     * @param administrator 要增加到数据库表中的Administrator对象
     * @param conn 来自Service的Connection对象
     * @return 成功增加true，失败：false
     * @throws SQLException
     */
    //增加单个顾客方法
    public boolean add(Administrator administrator,Connection conn) throws SQLException{
        //编辑预编译语句
        PreparedStatement add_pstmt = conn.prepareStatement("INSERT INTO administrator(id,no,name)" +
                "VALUES (?,?,?)");
        //设置预编译参数
        add_pstmt.setInt(1,administrator.getId());
        add_pstmt.setString(2,administrator.getNo());
        add_pstmt.setString(3,administrator.getName());
        //获取执行结果
        int affectedRow = add_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(add_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
    /**
     * @param conn  administrator 要更新到数据库表中的Administrator对象
     * @param conn 来自Service的Connection对象
     * @return update成功返回true，反之，false
     * @throws SQLException
     */
    //更新顾客信息方法
    public boolean update(Administrator administrator,Connection conn) throws SQLException{
        //编辑预编译语句
        PreparedStatement update_pstmt = conn.prepareStatement("UPDATE administrator SET" +
                " no=?,name=? WHERE id=?");
        //设置预编译参数
        update_pstmt.setInt(3,administrator.getId());
        update_pstmt.setString(1,administrator.getNo());
        update_pstmt.setString(2,administrator.getName());
        //获取执行结果
        int affectedRow = update_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(update_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
    /**
     * @param conn  customer 要删除数据库表中的Customer对象
     * @param conn 来自Service的Connection对象
     * @return 删除成功返回true，反之，false
     * @throws SQLException
     */
    //删除顾客方法
    public boolean delete(Integer id,Connection conn)throws SQLException{
        //设置预编译语句
        PreparedStatement delete_pstmt = conn.prepareStatement
                ("DELETE FROM administrator WHERE id=?");
        //设置预编译参数
        delete_pstmt.setInt(1,id);
        //获取执行结果
        int affectedRow = delete_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(delete_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
}
